var models = require('../db');
var express = require('express');
var router = express.Router();
var mysql = require('mysql');
var $sql = require('../sql');
const { loginInfo } = require('../sql');

// 连接数据库
var conn = mysql.createConnection(models.mysql);
conn.connect(function(err) {
  if (err) {
    console.error('error cg: ' + err.stack);
    return;
  }

  console.log('connected as id ' + conn.threadId);
});
// conn.connect();
// 宿舍统计
router.get('/countBuild', (req, res) => {
  debugger
  console.log("/countBuild" + req);
 
  var sql = $sql.dormDetail.countBuild
  // console.log("sql "+ sql);
    conn.query(sql,[], function(err, result) {
      // console.log(result);
      // console.log('----- ' + err.stack)
      var data = JSON.parse(JSON.stringify(result))
      // console.log(data);
      // 住满
      var sqlfull = $sql.dormDetail.dormLiveStatus +" WHERE a.capacity=a.liveNum GROUP BY buildNo"
    //   console.log(sqlfull);
      conn.query(sqlfull,[], function (err, result) {
        var datafull = JSON.parse(JSON.stringify(result))

        // console.error('----- ' + err.stack)
      
      // 未住满
      var sqlnotfull = $sql.dormDetail.dormLiveStatus +"WHERE a.capacity>a.liveNum and a.liveNum!=0 GROUP BY a.dormNo,a.buildNo"
      conn.query(sqlnotfull,[], function (err, result) {
        var datanotfull = JSON.parse(JSON.stringify(result))
        // 全空
        var sqlempty = $sql.dormDetail.dormLiveStatus +"WHERE a.liveNum =0 GROUP BY buildNo"
        conn.query(sqlempty,[], function (err, result) {
            var dataempty = JSON.parse(JSON.stringify(result))
            
            return res.send({
                status: 200,
                msg: "查询成功",
                list: data,
                full:datafull,
                notfull:datanotfull,
                empty:dataempty 
            })
        })
        })
    })
    })
  });
// 宿舍详情
router.post('/dormDetail', (req, res) => {
  var sql1 = $sql.dormDetail.floorNum 
  var sql = $sql.dormDetail.dormDetail
  var content =[]
  content.push(req.body.buildNo)
  if(req.body.dormtype !== ''){
    sql += " where capacity = ? "
    content.push(req.body.dormtype);
  }
  console.log("1+++++"+sql);
  console.log("content"+content);
  conn.query(sql1, [req.body.buildNo], function (err, result1) {
    var data1 = JSON.parse(JSON.stringify(result1))
    console.log("2+++++"+sql);
    conn.query(sql, content, function (err, result) {
      console.log(result)
      // try{
        var data = JSON.parse(JSON.stringify(result))
      // }
      // catch(err) {
      //   console.log('Errpr ++++ ' + err.message)
      // }
      
      return res.send({
      status: 200,
      msg: "查询成功",
      list: data,
      floor:data1
    })
  })
});
// 删除数据
router.post('/delDormNo', (req, res) => {
  var delDormNoList = req.body.delDormNo.split(/,|，|\s+/)
  var quy = new Array(delDormNoList.length).fill('?').join(',')
  var sql = `delete from apartment_info where dormNo in (${quy})`
  conn.query(sql, delDormNoList, function (err, result) {
      return res.send({
      status: 200,
      msg: "删除成功",
    })
  })
})
 /*  var sql = "select buildNo ,dormNo ,LEFT(dormNo,1) as floor from apartment_info where buildNo = ?"
  var sql1 ="select LEFT(dormNo,1) as floor from apartment_info where buildNo = ? group by LEFT(dormNo,1)"
  console.log(req.body.buildNo)
  conn.query(sql, [req.body.buildNo], function (err, result) {
    var data = JSON.parse(JSON.stringify(result))
    conn.query(sql1, [req.body.buildNo], function (err, result1) {
      var data1 = JSON.parse(JSON.stringify(result1))
      var list = [{buildNo:'14'}]
      console.log(data1[0].floor);
      for (let i = 0; i < data1.length; i++) {
        for (let j = 0; j<data.length; j++){
          if (data1[i].floor == data[j].floor){
            data1[i].children = data[j]
          }
        }
      }
      list[0].children = data1
      return res.send({
        status: 200,
        msg: "查询成功",
        list: list,
      })
    })
  })
}) */
});

module.exports = router;
